package binbase.web.gui

import static binbase.web.QueryUtil.*
import grails.converters.JSON
import groovy.sql.Sql
import javax.sql.DataSource
import org.codehaus.groovy.grails.commons.ConfigurationHolder

/**
 * used to query experiments
 */
class BBExperimentQueryController {

    DataSource dataSource

    def exportService


    def grailsApplication
    /**
     * lists all experiments as json string
     */
    def ajaxListExperimentsAsJSON = {

        def database = grailsApplication.config.binbase.database.toString()

        Sql sql = Sql.newInstance(dataSource)

        //result object
        def dataToRender = [:]
        dataToRender.aaData = []
        dataToRender.sEcho = params.sEcho
        dataToRender.iTotalRecords = sql.firstRow("select count(id) as id from BBExperiment where database_id in (select distinct id from bbdatabase where name = ?)", [database]).id
        dataToRender.iTotalDisplayRecords = dataToRender.iTotalRecords

        /**
         * query string for this, will be modified with limiting and sorting options
         */
        def query = new StringBuffer("""
            select * from BBExperiment where database_id in (select distinct id from bbdatabase where name = ?)
               """)


        buildQuery(params, query, [
                "id",
                "name",
                "title"
        ])

        sql.eachRow(query.toString(), [database], { def bin ->

            dataToRender.aaData << [
                    bin.id,
                    bin.name,
                    bin.title
            ]

        })

        /**
         * format the data as format and export them
         */
        if (params.format != null) {
            response.contentType = ConfigurationHolder.config.grails.mime.types[params.format]
            response.setHeader("Content-disposition", "attachment; filename=experiments.xls")

            def exports = []

            def fields = ["name", "title"]
            def labels = ["name": "Name", "title": "Title"]

            dataToRender.aaData.each {def t ->
                Object o = new Object()
                o.metaClass.name = t[1]
                o.metaClass.title = t[2]

                exports.add(o)
            }

            exportService.export(params.format, response.outputStream, exports, fields, labels, [:], [:])
        }
        /**
         * render the data as json to the controller
         */
        else {
            render dataToRender as JSON
        }
    }
}
